package com.example.fw;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;


public class DataBaseHelper extends HelperBase{

	private java.sql.Connection conn;
	
	public DataBaseHelper(ApplicationManager manager) {
		super(manager);
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		try {
			conn = DriverManager.getConnection(manager.getProperty("db.url"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public String getLastGroupId() {
		Statement st = null;
		ResultSet res = null;		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT MAX(group_id) FROM group_list");
			if (res.next()) {
				return res.getString(1);
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public String getLastContactId() {
		Statement st = null;
		ResultSet res = null;		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT MAX(contact_id) FROM contact_list");
			if (res.next()) {
				return res.getString(1);
			} else {
				return null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public Set<GroupObject> getGroups() {
		Set<GroupObject> groups = new HashSet<GroupObject>();
		Statement st = null;
		ResultSet res = null;		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT group_id,group_name,group_header,group_footer FROM group_list");
			while (res.next()) {
				groups.add(new GroupObject().setId(res.getString(1)).setName(res.getString(2)).setHeader(res.getString(3)).setFooter(res.getString(4)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return groups;
	}

	public Set<ContactObjects> getContacts() {
		Set<ContactObjects> contacts = new HashSet<ContactObjects>();
		Statement st = null;
		ResultSet res = null;		
		try {
			st = conn.createStatement();
			res = st.executeQuery("SELECT contact_id,contact_firstname,contact_lastname,contact_address,contact_home,contact_mobile,contact_work,contact_email,contact_email2,contact_bday,contact_bmonth,contact_byear,contact_group,contact_address2,contact_phone2 FROM contact_list");
			while (res.next()) {
				contacts.add(new ContactObjects().setId(res.getString(1)).setFirstname(res.getString(2)).setLastname(res.getString(3)).setAddress(res.getString(4)).setHome(res.getString(5)).setMobile(res.getString(6)).setWork(res.getString(7)).setEmail(res.getString(8)).setEmail2(res.getString(9)).setBday(res.getString(10)).setBmonth(res.getString(12)).setByear(res.getString(13)).setGroup(res.getString(14)).setAddress2(res.getString(15)).setPhone2(res.getString(16)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				res.close();
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		return contacts;
	}
}